package SiTuQingYou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import SiTuQingYou.util.DBHelper;
import SiTuQingYou.vo.Question;
import SiTuQingYou.vo.User;

public class UserDao
{
	private  UserDao() {}
	private static  UserDao ud = new  UserDao();
	public static  UserDao getud()
	{
		return ud;
	}	
	
	
	DBHelper dbHelper = DBHelper.getDB();
	
	
	//��¼
	public static User toLogin(String username,String userpass)
	{
		Connection conn= null;
		PreparedStatement ps= null;
		ResultSet  rs = null;
		
		DBHelper dbHelper = DBHelper.getDB();
		try 
		{
			
			conn = dbHelper.getConn();
			
			String sql = "select * from user where username=? and userpass=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			ps.setString(2, userpass);
			
			rs = ps.executeQuery();
			boolean f = rs.next();//ָ�������ƶ�һ��
			if(f==true)
			{
				User user = new User();
				user.setId(rs.getInt("id"));
				user.setUsername(rs.getString("username"));
				user.setUserpass(rs.getString("userpass"));
				user.setUserphone(rs.getString("userphone"));
				user.setUseranswer(rs.getString("useranswer"));
				user.setUsernickname(rs.getString("usernickname"));
				user.setUsersex(rs.getBoolean("usersex"));
				user.setUserrealname(rs.getString("userrealname"));
				
				Question question = new Question();
				question.setId(rs.getInt("questionid"));
				
				user.setQuestion(question);
				
				return user;
			}
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
		return null;
	}
	
	
	//�ж��û����Ƿ��ظ�.�ظ�����true�����ظ�����false
	public static boolean isRename(String username)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		DBHelper dbHelper = DBHelper.getDB();
		
		
		try 
		{
			conn = dbHelper.getConn();
			String sql = "select * from user where username='"+username+"'";
			ps = conn.prepareStatement(sql);
			
			rs = ps.executeQuery();
			boolean f = rs.next();
			
			return f;
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
		return false;
	}
	
	
	//ע�᣺д�����ݿ�
	public static void toRegisterNewCount(User user)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		DBHelper dbHelper = DBHelper.getDB();
		
		
		
		try 
		{
			conn = dbHelper.getConn();
			String sql = "insert into user(username,userpass,usersex,userrealname,userphone,usernickname,questionid,useranswer)value(?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			
			ps.setString(1, user.getUsername());
			ps.setString(2, user.getUserpass());
			ps.setBoolean(3, user.isUsersex());
			ps.setString(4, user.getUserrealname());
			ps.setString(5, user.getUserphone());
			ps.setString(6, user.getUsernickname());
			//ps.setInt(7, user.getQuestionid());
			ps.setInt(7, user.getQuestion().getId());
			ps.setString(8, user.getUseranswer());
			
			ps.executeUpdate();
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
		
		
	}
	
	public static String toLookForPassword(String username)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		DBHelper dbHelper = DBHelper.getDB();
		
		
		try 
		{
			conn = dbHelper.getConn();
			String sql = "select * from user,question where username=? and questionid=question.id ";
			ps = conn.prepareStatement(sql);
			ps.setString(1, username);
			
			rs = ps.executeQuery();
			

			if(rs.next())
			{
				String question = rs.getString("question.question");
				return question;
			}
			
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
		return null;
	}
	
	
	public static Boolean toCheckAnswer(String answer,String username)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		DBHelper dbHelper = DBHelper.getDB();
		
		
		try 
		{
			conn = dbHelper.getConn();
		    String sql = "select * from user,question where username=? and useranswer=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1,username);
			ps.setString(2,answer);
			
			rs = ps.executeQuery();
			boolean f = rs.next();//判断答案是否正确
			return f;
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
		return false;
	}
	
	
	public static void toChangePassword(String username,String password)
	{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		DBHelper dbHelper = DBHelper.getDB();
		
		
		try 
		{
			conn = dbHelper.getConn();
			String sql = "update user set userpass=? where username=? ";
			ps = conn.prepareStatement(sql);
			
			
			ps.setString(1, password);
			ps.setString(2, username);
			
			ps.executeUpdate();
		} 
		catch (SQLException e) 
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
	}
	
	
	public void revisePassword(int userid,String newpassword) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		
		try 
		{
			conn = dbHelper.getConn();
			String sql = "update user set userpass=? where id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, newpassword);
			ps.setInt(2, userid);
			ps.executeUpdate();
			
		} 
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally
		{
			dbHelper.close(rs, ps, conn);
		}
		
		
	}
	
	
	
	
	
	
	
}
